package com.inspur.smartclosing.generalledger.service.impl;

import com.inspur.edp.caf.db.dbaccess.DynamicResultRow;
import com.inspur.edp.data.connectors.exec.ExectorFactory;
import com.inspur.edp.data.connectors.exec.RelationalDbExector;
import com.inspur.smartclosing.generalledger.service.ClosingService;
import com.inspur.smartclosing.generalledger.utils.Dbservice;
import com.inspur.smartclosing.generalledger.utils.GeneralConfiguration;
import io.iec.edp.caf.boot.context.CAFContext;
import io.iec.edp.caf.commons.utils.SpringBeanUtils;
import io.iec.edp.caf.commons.utils.StringUtils;
import io.iec.edp.caf.rpc.api.service.RpcClient;
import lombok.extern.slf4j.Slf4j;
import org.jetbrains.annotations.NotNull;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestBody;

import java.sql.Clob;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Service
@Slf4j
public class ClosingServiceImpl implements ClosingService {
    /**
     *  平衡检查
     */
    @Override
    public Map<String,String> balanceCheck(@RequestBody Map<String,String> param){
        log.error("balanceCheck开始");
        Map<String, String> val = new HashMap<>();
        try {
            Dbservice db = new Dbservice();
            final String usercode = CAFContext.current.getSession().getUserCode();
            //解析入参
            String dwbh = param.get("dwbh");
            String kjnd = param.get("kjnd");
            String kjqj = param.get("kjqj");

            //获取上个期间年月
            Map<String,String> lastndqj = getLastPeriod(kjnd, kjqj);
            String lastNd = lastndqj.get("lastNd");
            String lastQj = lastndqj.get("lastQj");

            //获取单位范围
            List<DynamicResultRow> rts = getUnitRange(db, dwbh);
            List<Integer> pdList = new ArrayList<>();
            if (rts.size()!=0) {
                ExecutorService service = Executors.newFixedThreadPool(getMaxThead());
                CountDownLatch downLatch = new CountDownLatch(rts.size());
                for(DynamicResultRow rt : rts){//单位遍历
                    service.execute(new Runnable() {
                        @Override
                        public void run() {
                            synchronized(rt){
                                Map<String, String> zlcs = new HashMap<>();
                                try{
                                    Map<String, String> basic = new HashMap<>();
                                    basic.put("kjnd",kjnd);
                                    basic.put("kjqj",kjqj);
                                    basic.put("lastNd",lastNd);
                                    basic.put("lastQj",lastQj);

                                    String dwbh = rt.get("CODE").toString();
                                    basic.put("dwbh",dwbh);
                                    log.error("balanceCheck：检查单位=" + dwbh);
                                    String accorgid = rt.get("ACCORGID").toString();
                                    basic.put("accorgid",accorgid);
                                    String ledgerid = rt.get("ID").toString();
                                    basic.put("ledgerid",ledgerid);
                                    zlcs.put("dwbh", dwbh);
                                    zlcs.put("kjnd", kjnd);
                                    zlcs.put("kjqj", kjqj);
                                    zlcs.put("usercode", usercode);
                                    zlcs.put("sfgz", "0");
                                    //判断单位账期
                                    if(!ifclosing(db, ledgerid,kjnd,kjqj)){
                                        log.error("balanceCheck:"+ dwbh +"该账期未开账，不进行平衡检查");
                                        downLatch.countDown();
                                        return;
                                    }
                                    int jytgs = 0;
                                    int jybtgsq = 0;
                                    int jybtgsr = 0;
                                    String strSql =" select a.JCX, a.ID GZID, a.BZ, b.GZBH, b.GZQSSQLCS, b.GZQSSQL, a.JYLX  " +
                                            "from CloseAccountRules a " +
                                            "left join glVerifyingsql b on substr(a.gzbh,3)=b.gzbh " +
                                            "where a.MKBH='03' and (a.dwbh=:dwbh or a.dwbh='*') and b.sfzx='1' "+
                                            "order by b.GZBH ";
                                    Map<String, String> params = new HashMap<>();
                                    params.put("dwbh", dwbh);
                                    List<DynamicResultRow> rtRows = db.getDataListByPreparedSql(strSql,params);
                                    if (rtRows.size()>0 && rtRows.get(0) != null) {//规则遍历
                                        for(DynamicResultRow rtrow : rtRows){
                                            String gzsql = rtrow.get("GZQSSQL").toString().replace("@kjnd@",kjnd).replace("@lastNd@",lastNd);
                                            //组织参数
                                            String[] items = rtrow.get("GZQSSQLCS").toString().replaceAll("\\s", "").split(",");
                                            Map<String, String> args = new HashMap<>();
                                            for (String item : items) {
                                                args.put(item, basic.get(item));
                                            }
                                            try{
                                                log.error("balanceCheck:"+ dwbh +"的"+rtrow.get("GZBH").toString()+"规则参数："+args);
                                                List<DynamicResultRow> rels = db.getDataListByPreparedSql(gzsql,args);
                                                String jybz = rels.get(0).get("JYBZ").toString();
                                                String bz = rtrow.get("BZ").toString().replace("则","");
                                                if(jybz.equals("1")){//通过
                                                    jytgs += 1;
                                                }else{
                                                    if(rtrow.get("JYLX").toString().equals("01")) {//强校验
                                                        jybtgsq += 1;
                                                    }else {
                                                        jybtgsr += 1;
                                                    }
                                                    //完善不通过原因
                                                    String gzbh = rtrow.get("GZBH").toString();
                                                    if(gzbh.equals("030101")){//强校验【科目未结平】需要显示哪些科目未结平
                                                        String sql = "select rtrim(xmlagg(xmlelement(e, a.ACCTITLECODE||c.FULLNAME_CHS,',').extract('//text()')).getclobval(),',') KMBH " +
                                                                "from FIGLAccountBalance" + kjnd + " a " +
                                                                "left join BFAccountTitle" + kjnd + " c on a.ACCTITLECODE=c.code " +
                                                                "where a.LEDGER = :ledgerid " +
                                                                "and exists(select 1 from BFAccTitleAccRlat" + kjnd + "  b where a.LEDGER=b.LEDGER and a.ACCPERIODCODE = :kjqj and a.ACCTITLECODE=b.accounttitlecode and b.ENDPERIODBAL='1') " +
                                                                "and nvl(ENDBALANCEAMT,0)<>0 ";
                                                        Map<String, String> cs = new HashMap<>();
                                                        cs.put("ledgerid", ledgerid);
                                                        cs.put("kjqj", kjqj);
                                                        bz = db.getDataListByPreparedSql(sql,cs).get(0).get("KMBH").toString() + "科目未结平";
                                                    } else if ("0001".equals(dwbh.substring(dwbh.length()-4)) && (gzbh.equals("030418") || gzbh.equals("030420"))) {//290203、290201全省合并数据核对不一致时，直接显示出哪个地市不一致
                                                        List<String> dwList = new ArrayList<>();
                                                        //查询该省所有地市单位
                                                        String dwsql = "select CODE from bfledger where code like '" + dwbh.substring(0, dwbh.length() -4) + "%' and substr(code,-4) not in ('0000','0001','0009') and ISDISABLE='0' ";
                                                        List<DynamicResultRow> dwrts = db.getDataListBysql(dwsql);
                                                        //循环规则
                                                        for(DynamicResultRow dw : dwrts){
                                                            args.put("dwbh", dw.get("CODE").toString());
                                                            log.error("balanceCheck:完善不通过原因#："+ args);
                                                            String jyjg = db.getDataListByPreparedSql(gzsql,args).get(0).get("JYBZ").toString();
                                                            if(jyjg.equals("0")){
                                                                dwList.add(dw.get("CODE").toString());
                                                            }
                                                        }
                                                        bz = String.join(",",dwList)+ "地市余额不相等、或方向一致";
                                                    }
                                                }
                                                //校验结果表处理
                                                processingCheckResult(db, dwbh, kjnd, kjqj, rtrow.get("GZID").toString(), jybz, bz);
                                            }catch (Exception e){
                                                log.error("balanceCheck报错：" + e.getMessage(), e);
                                                //校验结果表处理
                                                processingCheckResult(db, dwbh, kjnd, kjqj, rtrow.get("GZID").toString(), "0", "校验失败，请联系管理员");
                                            }

                                        }
                                    }
                                    //组织参数：关账信息总览表
                                    String sftg = "0";
                                    String jg = "总账平衡检查未通过，请查看明细详情";
                                    if(jybtgsq == 0){
                                        sftg = "1";
                                        jg = "总账平衡检查通过";
                                    }
                                    if(jybtgsq != 0 || jybtgsr != 0){
                                        pdList.add(1);
                                    }
                                    zlcs.put("jyxzs", String.valueOf(jytgs+jybtgsq+jybtgsr));
                                    zlcs.put("jytgs", String.valueOf(jytgs));
                                    zlcs.put("jybtgs", String.valueOf(jybtgsq+jybtgsr));
                                    zlcs.put("sftg", sftg);
                                    zlcs.put("jg", jg);
                                }catch (Exception e){
                                    log.error("balanceCheck检查报错：" + dwbh + e.getMessage(), e);
                                    zlcs.put("jyxzs", "0");
                                    zlcs.put("jytgs", "0");
                                    zlcs.put("jybtgs", "0");
                                    zlcs.put("sftg", "0");
                                    zlcs.put("jg", "总账平衡检查异常，请联系管理员");
                                    pdList.add(1);
                                }
                                overviewCloseResult(db, zlcs);
                                downLatch.countDown();
                            }
                        }
                    });

                }
                downLatch.await(20, TimeUnit.MINUTES);
                service.shutdown();
                if(pdList.size() > 0){
                    val.put("0","总账模块已执行检查操作，部分未通过");
                }else{
                    val.put("1","总账模块已执行检查操作，全部通过");
                }
            }else{
                val.put("0","总账模块已执行检查操作，部分未通过");
            }
        }catch (Exception e){
            val.put("0","总账模块已执行检查操作，部分未通过");
            log.error("balanceCheck报错：" + e.getMessage(), e);
        }
        return val;
    }

    /**
     *  月末结转
     */
    private RpcClient rpcClent = SpringBeanUtils.getBean(RpcClient.class);

    @Override
    public Map<String,String> monthlyBalance(@RequestBody Map<String,String> param){
        log.error("monthlyBalance开始");
        Map<String, String> val = new HashMap<>();
        try {
            Dbservice db = new Dbservice();
            final String usercode = CAFContext.current.getSession().getUserCode();
            String dwbh = param.get("dwbh");
            String kjnd = param.get("kjnd");
            String kjqj = param.get("kjqj");
            List<String> returnList = new ArrayList<>();
            //判断账期
            if(kjqj.equals("13")){
                val.put("0","该年最后一个期间请进行年结。");
                return val;
            }

            //获取上个账期年月
            Map<String,String> lastndqj = getLastPeriod(kjnd, kjqj);
            String lastNd = lastndqj.get("lastNd");
            String lastQj = lastndqj.get("lastQj");

            //获取单位范围
            List<DynamicResultRow> rts = getUnitRange(db, dwbh);
            if (rts.size()!=0) {
                ExecutorService service = Executors.newFixedThreadPool(getMaxThead());
                CountDownLatch downLatch = new CountDownLatch(rts.size());
                for(DynamicResultRow rt : rts){//单位多线程遍历
                    service.execute(new Runnable() {
                        @Override
                        public void run() {
                            synchronized(rt){
                                Map<String, String> zlcs = new HashMap<>();
                                try{
                                    Map<String, String> basic = new HashMap<>();
                                    basic.put("kjnd",kjnd);
                                    basic.put("kjqj",kjqj);
                                    basic.put("lastNd",lastNd);
                                    basic.put("lastQj",lastQj);

                                    String dwbh = rt.get("CODE").toString();
                                    basic.put("dwbh",dwbh);
                                    log.error("monthlyBalance：关账单位=" + dwbh);
                                    String accorgid = rt.get("ACCORGID").toString();
                                    basic.put("accorgid",accorgid);
                                    String ledgerid = rt.get("ID").toString();
                                    basic.put("ledgerid",ledgerid);
                                    zlcs.put("dwbh", dwbh);
                                    zlcs.put("kjnd", kjnd);
                                    zlcs.put("kjqj", kjqj);
                                    zlcs.put("usercode", usercode);
                                    //单位期间状态校验
                                    if(!ifclosing(db, ledgerid, kjnd, kjqj)){
                                        log.error("monthlyBalance:该账期未开账");
                                        returnList.add( "地市"+dwbh +"该账期未开账，无法进行关账");
                                        downLatch.countDown();
                                        return;
                                    }
                                    //检查单位是否进行平衡检查
                                    if(!pdsfjxphjc(db,dwbh,kjnd,kjqj)){
                                        log.error("monthlyBalance:地市"+dwbh +"该账期未进行总账平衡检查");
                                        returnList.add( "地市"+dwbh +"该账期未进行总账平衡检查，无法进行关账");
                                        downLatch.countDown();
                                        return;
                                    }
                                    String strSql  = "select count(1) from Figlperiodstate" + kjnd + "  where ACCLEDGERID = :ledgerid and PERIODNUMBER < :kjqj and PERIODSTATE='3' " ;
                                    Map<String, String> params = new HashMap<>();
                                    params.put("ledgerid", ledgerid);
                                    params.put("kjqj", kjqj);
                                    int nums = db.getDataCountByPreparedSql(strSql,params);
                                    if(nums!=0){
                                        log.error("monthlyBalance:地市"+dwbh +"该账期之前的账期需要关账，请检查。");
                                        zlcs.put("sfgz", "0");
                                        zlcs.put("jg", "该账期之前的账期需要全部关账");
                                        gzgxsj(db, zlcs);
                                        returnList.add( "地市"+dwbh +"该账期之前的账期存在未关账，无法进行关账");
                                        downLatch.countDown();
                                        return;
                                    }
                                    //查询该单位下的所有校验规则
                                    strSql =" select a.SJJYX, b.GZBH, b.GZQSSQLCS, b.GZQSSQL " +
                                            "from CloseAccountRules a " +
                                            "left join glVerifyingsql b on substr(a.gzbh,3)=b.gzbh " +
                                            "where a.MKBH='03' and (a.dwbh=:dwbh or a.dwbh='*') and a.JYLX = '01' and b.sfzx='1'  " +
                                            "order by b.GZBH ";
                                    Map<String, String> params0= new HashMap<>();
                                    params0.put("dwbh", dwbh);
                                    List<DynamicResultRow> rtRows = db.getDataListByPreparedSql(strSql,params0);
                                    if (rtRows.size()>0 && rtRows.get(0) != null){//强校验规则遍历
                                        for(DynamicResultRow rtrow : rtRows){
                                            strSql = rtrow.get("GZQSSQL").toString().replace("@kjnd@",kjnd).replace("@lastNd@",lastNd);
                                            //组织参数
                                            String[] items = rtrow.get("GZQSSQLCS").toString().replaceAll("\\s", "").split(",");
                                            Map<String, String> args = new HashMap<>();
                                            for (String item : items) {
                                                args.put(item, basic.get(item));
                                            }
                                            List<DynamicResultRow> rels = db.getDataListByPreparedSql(strSql,args);
                                            if(!rels.get(0).get("JYBZ").toString().equals("1")){//只要校验不通过该单位结束
                                                zlcs.put("sfgz", "0");
                                                zlcs.put("jg", "未通过"+rtrow.get("GZBH").toString()+"规则平衡检查("+rtrow.get("SJJYX").toString()+")");
                                                gzgxsj(db, zlcs);
                                                returnList.add( "地市"+dwbh +"未通过"+rtrow.get("GZBH").toString()+"规则平衡检查("+rtrow.get("SJJYX").toString()+")");
                                                downLatch.countDown();
                                                return;
                                            }
                                        }
                                    }
                                    //通过所有校验，调用产品月末结转接口
                                    LinkedHashMap<String,Object> linkedmap = new LinkedHashMap<>();
                                    HashMap<String ,Object> map4Balance = new HashMap<>();
                                    map4Balance.put("year",kjnd);
                                    map4Balance.put("periodNo",kjqj);
                                    map4Balance.put("ledger",ledgerid);
                                    linkedmap.put("param",map4Balance);
                                    HashMap<String,Object> result = rpcClent.invoke(HashMap.class, "com.inspur.gs.fi.gl.accountbalance.eapi.ReferenceCheckService.setGLEndPeriod","gl",linkedmap,null);
                                    boolean cons = (Boolean) result.get("result");
                                    if(cons){//该单位关账成功，更新关账信息总览表
                                        zlcs.put("sfgz", "1");
                                        zlcs.put("jg", "总账关账完成");
                                    }else{
                                        zlcs.put("sfgz", "0");
                                        zlcs.put("jg", "月末结转失败("+result.get("message")+")");
                                        log.error("monthlyBalance:产品接口result=="+result.get("message"));
                                        returnList.add( "地市"+dwbh +result.get("message"));
                                    }
                                }catch(Exception e){
                                    log.error("monthlyBalance报错：" +dwbh+ e.getMessage(), e);
                                    zlcs.put("sfgz", "0");
                                    zlcs.put("jg", "总账关账异常，请联系管理员");
                                    returnList.add( "地市"+dwbh +"总账关账异常，请联系管理员");
                                }
                                gzgxsj(db, zlcs);
                                downLatch.countDown();

                            }
                        }
                    });

                }
                downLatch.await(20, TimeUnit.MINUTES);
                service.shutdown();
                if(returnList.size() > 0){
                    val.put("2",String.join(";", returnList));
                }else{
                    val.put("1","总账关账完成");
                }
            }else{
                val.put("0","未获取到总账关账的单位信息");
            }
        }catch (Exception e){
            val.put("0","总账关账异常，请联系管理员！");
            log.error("monthlyBalance报错：" + e.getMessage(), e);
        }
        return val;
    }


    /**
     *  是否关账
     */
    public boolean ifclosing(Dbservice db, String ledgerid, String kjnd, String kjqj){
        boolean state = false;
        try {
            String strSql = "select PERIODSTATE from Figlperiodstate" + kjnd + " where ACCLEDGERID = :ledgerid and PERIODNUMBER = :kjqj " ;
            Map<String, String> params = new HashMap<>();
            params.put("ledgerid", ledgerid);
            params.put("kjqj", kjqj);
            List<DynamicResultRow> resultRows = db.getDataListByPreparedSql(strSql, params);
            if (resultRows.size()>0 && resultRows.get(0) != null) {
                String  periodstate = resultRows.get(0).get("PERIODSTATE").toString();
                if(periodstate.equals("3")){
                    state = true;
                }
            }
            log.error("ifclosing：获取单位"+ledgerid+"的state: " + state);
        }catch (Exception e){
            log.error("ifclosing报错：" + e.getMessage(), e);
        }
        return state;
    }
    /**
     *  是否关账
     */
    public boolean pdsfjxphjc(Dbservice db, String dwbh, String kjnd, String kjqj){
        boolean state = false;
        try {
            String strSql = "select count(1) from CloseAccountSchedule where dwbh = :dwbh and nd = :kjnd and qj = :kjqj and mkbh = '03'" ;
            Map<String, String> params = new HashMap<>();
            params.put("dwbh", dwbh);
            params.put("kjqj", kjqj);
            params.put("kjnd", kjnd);
            int count = db.getDataCountByPreparedSql(strSql,params);
            if(count > 0){
                state = true;
            }
        }catch (Exception e){
            log.error("balanceCheck报错：" + e.getMessage(), e);
        }
        return state;
    }

    /**
     *  获取上个期间年月
     */
    public Map<String,String> getLastPeriod(String kjnd, String kjqj) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        Date yyyyMM = sdf.parse(kjnd + kjqj);
        Calendar cal = Calendar.getInstance();
        cal.setTime(yyyyMM);
        cal.add(Calendar.MONTH, -1);
        String strLastMonth = sdf.format(cal.getTime());
        String lastNd = strLastMonth.substring(0,4);
        String lastQj = strLastMonth.substring(4,6);
        if("01".equals(kjqj)){
            lastQj = "13";
        }
        Map<String, String> lastndqj = new HashMap<>();
        lastndqj.put("lastNd", lastNd);
        lastndqj.put("lastQj", lastQj);
        return lastndqj;
    }

    /**
     *  获取单位范围
     */
    public List<DynamicResultRow> getUnitRange(Dbservice db, String dwbh){
        String getorgWhere = getDWsql(dwbh);
        String strSql = "select ID, ACCORGID, CODE from bfledger where " + getorgWhere + " and ISDISABLE='0' ";
        log.error("balanceCheck：strSql=" + strSql);
        List<DynamicResultRow> rts = db.getDataListBysql(strSql);
        return rts;
    }

    private static String getDWsql(String dwbh) {
        String getorgWhere;
        // 判断是否查所有
        if ("000000".equals(dwbh)) {
            getorgWhere = " substr(code,-4)<>'0000' ";
        }
        //判断是否总部
        else if("100000".equals(dwbh.substring(dwbh.length()-6))){
            if (dwbh.length() == 6) {
                // 查6位dwbh的账套
                getorgWhere = " code not like '0%' and substr(code,-4)<>'0000'";
            } else {
                // 查8位dwbh的账套
                getorgWhere = " code like '" + dwbh.substring(0, dwbh.length() - 6) + "%' and substr(code,-4)<>'0000'";
            }
        }
        //判断是否省分
        else if("0000".equals(dwbh.substring(dwbh.length()-4))){
            getorgWhere = " code like '" + dwbh.substring(0, dwbh.length() -4) + "%' and substr(code,-4)<>'0000'";
        }
        //判断是否地市
        else{
            getorgWhere = " code = '" + dwbh + "'";
        }
        return getorgWhere;
    }

    /**
     *  校验结果表处理
     */
    public void processingCheckResult(Dbservice db, String dwbh, String kjnd, String kjqj, String gzid, String jybz, String ycmx){
        log.error("校验结果表处理规则gzid=="+gzid+"的结果:"+jybz);
        final String usercode = CAFContext.current.getSession().getUserCode();
        String jyid = UUID.randomUUID().toString();
        String ycid = "";
        Map<String, String> params = new HashMap<>();
        params.put("dwbh", dwbh);
        params.put("kjnd", kjnd);
        params.put("kjqj", kjqj);
        params.put("jybz", jybz);
        params.put("usercode", usercode);
        params.put("gzid", gzid);
        params.put("jyid", jyid);
        params.put("ycid", ycid);

        String strSql = "begin " +
                "delete from CloseAccountDetail where GZID = :gzid and DWBH = :dwbh and ND = :kjnd and QJ = :kjqj ; " +
                "delete from CloseAccountErr a where exists(select 1 from CloseAccountDetail b where GZID = :gzid and DWBH = :dwbh and ND = :kjnd and QJ = :kjqj and b.JYXX = a.ID) ; " +
                "insert into CloseAccountDetail(ID, GZID, DWBH, ND, QJ, JYBZ, CREATOR, CREATEDTIME,  JYXX) " +
                "values(:jyid, :gzid, :dwbh, :kjnd, :kjqj, :jybz, :usercode, sysdate, :ycid ) ; ";

        if(!jybz.equals("1")){//不通过校验
            ycid = UUID.randomUUID().toString();
            params.put("ycid", ycid);
            params.put("ycmx", ycmx);
            strSql +=  "insert into CloseAccountErr(ID, MXID, YCXM, CREATOR, CREATEDTIME) " +
                    "values(:ycid, :jyid, :ycmx, :usercode, sysdate) ; ";
        }
        strSql += " end; ";
        db.executeByPreparedSql(strSql,params);
    }

    /**
     *  关账信息总览表处理
     */
    public void overviewCloseResult(Dbservice db, Map<String, String> zlcs){
        String sql = "merge into CloseAccountSchedule A " +
                "using (select  sys_guid() ID, '03' MKBH,  :dwbh DWBH, :kjnd ND, :kjqj QJ, :sfgz SFGZ, :jyxzs JYXZS, :jytgs JYTGS, :jybtgs JYBTGS, :sftg SFTG, :jg GZJG, " +
                ":usercode CREATOR, sysdate CREATEDTIME, :usercode LASTMODIFIER, sysdate LASTMODIFIEDTIME from dual) B " +
                "ON (A.MKBH=B.MKBH and A.DWBH=B.DWBH and A.ND=B.ND and A.QJ=B.QJ) " +
                "WHEN MATCHED THEN " +
                "UPDATE SET A.SFGZ = B.SFGZ, A.JYXZS=B.JYXZS, A.JYTGS=B.JYTGS, A.JYBTGS=B.JYBTGS, A.SFTG=B.SFTG, A.GZJG=B.GZJG, A.LASTMODIFIER=B.LASTMODIFIER, A.LASTMODIFIEDTIME=B.LASTMODIFIEDTIME " +
                "WHEN NOT MATCHED THEN " +
                "INSERT (A.ID, A.MKBH, A.DWBH, A.ND, A.QJ, A.SFGZ, A.JYXZS, A.JYTGS, A.JYBTGS, A.SFTG, A.GZJG, A.CREATOR, A.CREATEDTIME, A.LASTMODIFIER, A.LASTMODIFIEDTIME) " +
                "VALUES (B.ID, B.MKBH, B.DWBH, B.ND, B.QJ, B.SFGZ, B.JYXZS, B.JYTGS, B.JYBTGS, B.SFTG, B.GZJG, B.CREATOR, B.CREATEDTIME, B.LASTMODIFIER, B.LASTMODIFIEDTIME) ";
        db.executeByPreparedSql(sql,zlcs);
    }
    /**
     *  关账更新数据
     */
    public void gzgxsj(Dbservice db, Map<String, String> zlcs ){
        String sql = "update CloseAccountSchedule set sfgz = :sfgz,gzjg = :jg,LASTMODIFIER = :usercode where dwbh = :dwbh and nd = :kjnd and qj = :kjqj and mkbh = '03'";
        db.executeByPreparedSql(sql,zlcs);
    }


    /**
     *  查询最大线程数
     */
    public int getMaxThead(){
        int maxThead;
        try {
            maxThead = Integer.parseInt(GeneralConfiguration.getValuesConfig("zngzmaxThead"));
        } catch (Exception ignored) {
            maxThead =10;
        }
        return maxThead;
    }
    /**
     *  同步状态表
     */
    @Override
    public List<Map<String,String>> SynchronizeState(Map<String, String> param) {
        String zb = param.get("zb");
        String nd = param.get("kjnd");
        String qj = param.get("kjqj");
        String dwbh = param.get("dwbh");
        String pageNumStr = param.get("pageNum");
        String pageSizeStr= param.get("pageSize");
        List<Map<String,String>> returnListMap = new ArrayList<>();
        log.error("参数 ================================= "+ param);
        if(!StringUtils.isEmpty(nd) && !StringUtils.isEmpty(qj) && !StringUtils.isEmpty(dwbh) && !StringUtils.isEmpty(pageNumStr)
                && !StringUtils.isEmpty(pageSizeStr)&& !StringUtils.isEmpty(zb)){
            try{
                Dbservice db = new Dbservice();
                int pageNum = Integer.parseInt(pageNumStr);
                int pageSize = Integer.parseInt(pageSizeStr);
                String getWhere = getDWsql(dwbh);
                String dwbhCxSql = "select DWBH FROM (" +
                        "select DWBH,ROWNUM AS FASTROWNUM from (with TRULES as " +
                        "         (SELECT MAX(ID) AS ID,DWBH,ND,QJ, " +
                        "  CASE " +
                        "                     WHEN LENGTH(DWBH) = 6 " +
                        "                         THEN '01' " +
                        "                     WHEN DWBH LIKE '02%' " +
                        "                         THEN '02' " +
                        "                     WHEN DWBH LIKE '03%' " +
                        "                         THEN '03' " +
                        "                     WHEN DWBH LIKE '05%' " +
                        "                         THEN '05' " +
                        "                     WHEN DWBH LIKE '06%' " +
                        "                         THEN '06' " +
                        "                     ELSE NULL " +
                        "                     END   AS ZB " +
                        "          FROM CLOSEACCOUNTSCHEDULE  " +
                        "          GROUP BY DWBH, ND, QJ) " +
                        " SELECT BFLEDGER.CODE AS DWBH " +
                        " FROM BFLEDGER " +
                        "         LEFT JOIN TRULES " +
                        "                   ON BFLEDGER.CODE = TRULES.DWBH " +
                        " where 1 = 1  and BFLEDGER.isdisable = '0' " +
                        " and  (ZB = :zb AND ND = :nd AND QJ = :qj) AND ("+ getWhere + ") " +
                        " order by DWBH  ) A where RowNum  < "+(pageNum*pageSize+1)+" ) A WHERE FASTROWNUM  > "+((pageNum-1)*pageSize);
                Map<String,String> dwbhCxCs = new HashMap<>();
                dwbhCxCs.put("zb",zb);
                dwbhCxCs.put("nd",nd);
                dwbhCxCs.put("qj",qj);
                List<DynamicResultRow> dwbhList =  db.getDataListByPreparedSql(dwbhCxSql,dwbhCxCs);
                log.error("查询到的单位数量=================="+ dwbhList.size());
                if(dwbhList.size() == 0){
                    return returnListMap;
                }
                List<String> dwbhs = new ArrayList<>();
                for(int i= 0 ;i < dwbhList.size() ; i++){
                    dwbhs.add(dwbhList.get(i).get("DWBH").toString());
                }
                log.error("dwbhs========================="+dwbhs);
                List<Map<String,String>> zzData =  getZztbsj(db,dwbhs,nd,qj);
                List<Map<String,String>> zcData =  asset_zt(dwbhs,nd,qj);
                List<Map<String,String>> gcData = SFcloseaccount(dwbhs,nd,qj);
                log.error("zzData=================="+zzData);
                log.error("zcData=================="+zcData);
                log.error("gcData=================="+gcData);
                List<Map<String,String>> mergedList = Stream.concat(Stream.concat(zzData.stream(), zcData.stream()), gcData.stream())
                        .collect(Collectors.toList());
                List<String> zzDwGbList =  new ArrayList<>();
                List<String> zzDwKqList =  new ArrayList<>();
                List<String> zcDwGbList =  new ArrayList<>();
                List<String> zcDwKqList =  new ArrayList<>();
                List<String> gcDwGbList =  new ArrayList<>();
                List<String> gcDwKqList =  new ArrayList<>();
                List<String> czDwGbList =  new ArrayList<>();
                List<String> czDwKqList =  new ArrayList<>();
                for(Map<String,String> map : mergedList){
                    if(map.keySet().size() == 0) continue;
                    if(map.get("MKBH").equals("03")){
                        if(map.get("ZT").equals("1")){
                            zzDwGbList.add(map.get("DWBH"));
                            czDwGbList.add(map.get("DWBH"));
                        }
                        if(map.get("ZT").equals("0")){
                            zzDwKqList.add(map.get("DWBH"));
                            czDwKqList.add(map.get("DWBH"));
                        }
                    }else if(map.get("MKBH").equals("02")){
                        if(map.get("ZT").equals("1")){
                            zcDwGbList.add(map.get("DWBH"));
                        }
                        if(map.get("ZT").equals("0")){
                            zcDwKqList.add(map.get("DWBH"));
                        }
                    }else if(map.get("MKBH").equals("01")){
                        if(map.get("ZT").equals("1")){
                            gcDwGbList.add(map.get("DWBH"));
                        }
                        if(map.get("ZT").equals("0")){
                            gcDwKqList.add(map.get("DWBH"));
                        }
                    }
                }
                Map<String,Map<String,List>> xhData = new HashMap<>();
                xhData.put("04",new HashMap<String,List>(){{put("1",czDwGbList);put("0",czDwKqList);}});
                xhData.put("03",new HashMap<String,List>(){{put("1",zzDwGbList);put("0",zzDwKqList);}});
                xhData.put("02",new HashMap<String,List>(){{put("1",zcDwGbList);put("0",zcDwKqList);}});
                xhData.put("01",new HashMap<String,List>(){{put("1",gcDwGbList);put("0",gcDwKqList);}});
                for(String mkbh : xhData.keySet()){
                    Map<String,List> map2 = xhData.get(mkbh);
                    for(String sfgz : map2.keySet()){
                        List<String> tbdwbhList = map2.get(sfgz);
                        if(tbdwbhList.size() == 0) continue;
                        String updateSql = "update CloseAccountSchedule set sfgz = :sfgz , sftg = :sfgz where  nd = :nd and qj = :qj and mkbh = :mkbh  and dwbh in (";
                        Map<String,String> map1 = new HashMap<>();
                        map1.put("mkbh",mkbh);
                        map1.put("nd",nd);
                        map1.put("qj",qj);
                        map1.put("sfgz",sfgz);
                        int i = 0;
                        for(String tbdwbh:tbdwbhList){
                            updateSql+=":tbdwbh"+i+",";
                            map1.put("tbdwbh"+i,tbdwbh);
                            i++;
                        }
                        updateSql = updateSql.substring(0, updateSql.length() - 1) + " )";
                        log.error("同步状态的sql ================================= "+ updateSql);
                        log.error("同步状态的参数 ================================= "+ map1);
                        if(i > 0){
                            db.executeByPreparedSql(updateSql,map1);
                        }
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
                log.error("同步状态执行失败");
            }
        }
        return returnListMap;
    }
    /**
     * 总账同步方法
     */
    private static List<Map<String,String>> getZztbsj(Dbservice db,List<String> dwbhs, String nd, String qj){
        StringBuilder selectStateSql = new StringBuilder();
        selectStateSql.append("select b.code,PERIODSTATE from figlperiodstate"+nd+" a " +
                " left join BFLedger b on b.ACCORGID = a.ACCORGID"+
                " where PERIODNUMBER = :qj and b.isdisable = '0' and b.code in (");
        Map<String,String> map = new HashMap<>();
        map.put("qj",qj);
        for(int i= 0 ;i < dwbhs.size() ; i++){
            selectStateSql.append(":cs"+i);
            if(i < dwbhs.size() -1){
                selectStateSql.append(",");
            }
            map.put("cs"+i,dwbhs.get(i));
        }
        selectStateSql.append(")");
        List<DynamicResultRow> dwztData =  db.getDataListByPreparedSql(selectStateSql.toString(),map);
        List<Map<String,String>> returnListMap = new ArrayList<>();
        for(DynamicResultRow dynamicResultRow : dwztData){
            Map<String,String> returnMap = new HashMap<>();
            String state = dynamicResultRow.get("PERIODSTATE").toString();
            if(state.equals("3")){
                returnMap.put("ZT","0");
            }else if(state.equals("2")){
                returnMap.put("ZT","1");
            }else{
                continue;
            }
            returnMap.put("DWBH",dynamicResultRow.get("CODE").toString());
            returnMap.put("MKBH","03");
            returnListMap.add(returnMap);
        }
        return returnListMap;
    }

    /**
     * 资产同步方法
     * @param dwbhs
     * @param year
     * @param kjqj
     * @return
     */
    public List<Map<String,String>> asset_zt(List<String> dwbhs,String year,String kjqj) {
        List<Map<String,String>> list = new ArrayList<>();
        Dbservice db = new Dbservice();
        String new_year = "";
        String new_kjqj = "";
        if("12".equals(kjqj)) {
            new_year = String.valueOf(Integer.parseInt(year) + 1);
            new_kjqj = "01";
        }else {
            new_year = year;
            new_kjqj = String.valueOf(Integer.parseInt(kjqj) +1);
        }

        log.error("newyear :{}",new_year);
        log.error("newmonth === {}", new_kjqj);

        String sourceId = getCloudSource();
        if (StringUtils.isEmpty(sourceId)) {
            log.error("获取数据源失败");
        }
        RelationalDbExector exector = ExectorFactory.create(sourceId);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < dwbhs.size(); i++) {
            // 在每个元素前后添加单引号，并处理最后一个元素后的逗号
            sb.append("'").append(dwbhs.get(i)).append("'");
            if (i < dwbhs.size() - 1) {
                sb.append(",");
            }
        }
        String in_dwbh = sb.toString();
        String sql = "SELECT ORGID,DBCODE FROM GSPDBEXTSETTING WHERE ORGID  in (" + in_dwbh + ")";
        List<DynamicResultRow> dbCodeList = db.getDataListBysql(sql);
        if (dbCodeList.size() > 0 && !dbCodeList.isEmpty()) {

            Map<String, List<DynamicResultRow>> dwbhByDbcodeMap = dbCodeList.stream().
                    collect(Collectors.groupingBy(row -> row.get("DBCODE").toString(),
                            Collectors.toList()));
            for (Map.Entry<String,List<DynamicResultRow>> entry : dwbhByDbcodeMap.entrySet()) {
                String fenku = entry.getKey();
                List<DynamicResultRow> dwbhList = entry.getValue();
                StringBuilder sb2 = new StringBuilder();
                for (int i = 0; i < dwbhList.size(); i++) {
                    // 在每个元素前后添加单引号，并处理最后一个元素后的逗号
                    sb2.append("'").append(dwbhList.get(i).get("ORGID").toString()).append("'");
                    if (i < dwbhList.size() - 1) {
                        sb2.append(",");
                    }
                }
                String sql2 = "SELECT LSGSCS_HSDW,CASE WHEN LSGSCS_VALUE = '" + new_kjqj + "' then '1' else '0' end NUM FROM LSGSCS" + new_year + "@to_" + fenku + " WHERE LSGSCS_HSDW in (" + sb2.toString() + ") AND " +
                        " LSGSCS_MKID = 'GD' AND LSGSCS_KEY = 'GD_KJQJ'";
                List<Map<String, String>> countList = getDatas(exector, sql2);
                if (!countList.isEmpty() && countList != null && countList.size() > 0) {
                    for (Map<String, String> map : countList) {
                        Map<String,String> jsonObject = new HashMap<>();
                        String num = map.get("NUM");
                        String dwbh = map.get("LSGSCS_HSDW");

                        jsonObject.put("ZT", num);
                        jsonObject.put("DWBH", dwbh);
                        jsonObject.put("MKBH", "02");
                        list.add(jsonObject);
                    }
                }
            }
        }
        return list;
    }

    private String getCloudSource(){
        Dbservice db = new Dbservice();
        String dataSource = "";
        List<DynamicResultRow> dataSources = db.getDataListBysql("select id from gspdatasourceentity where code='CLOUDDATASOURCE'");
        if (dataSources != null && dataSources.get(0) != null) {
            dataSource = dataSources.get(0).get("ID").toString();
        }
        return dataSource;
    }

    private List<Map<String, String>> getDatas(RelationalDbExector exector, String s) {

        List<Map<String, String>> results;
        try {
            ResultSet rs = exector.query(s, null);
            results = convertList(rs);
        } catch (SQLException e) {
            results = new ArrayList<>();
            log.error("使用cloud数据源查询失败，sql:" + s);
        }
        return results;
    }

    private static List<Map<String, String>> convertList(ResultSet rs) throws SQLException {
        List<Map<String, String>> list = new ArrayList<>();
        //获取键名
        ResultSetMetaData md = rs.getMetaData();
        //获取列的数量
        int columnCount = md.getColumnCount();
        while (rs.next()) {
            //声明Map
            Map<String, String> rowData = new HashMap<>();
            String value;
            for (int i = 1; i <= columnCount; i++) {
                if (rs.getObject(i) instanceof Clob) {
                    Clob clob = (Clob) rs.getObject(i);
                    value = clob.getSubString(1, (int) clob.length());
                } else {
                    value = rs.getObject(i).toString();
                }
                //获取键名及值
                rowData.put(md.getColumnName(i).toUpperCase(), value);
            }
            list.add(rowData);
        }
        return list;
    }
    /**
     * 工程同步方法
     */
    public List<Map<String,String>> SFcloseaccount(List<String> dwbhs, String year, String qj) throws Exception {
        Dbservice db = new Dbservice();
        String strSql = "";
        List<Map<String,String>> result = new ArrayList<>();

        for(String dwbh1:dwbhs){
            Map<String,String> returnObject = new HashMap<>();
            try{
                strSql = String.format("SELECT count(1) FROM ZWBIZCHECKDETAIL@Fs_Cloud_six " +
                        "WHERE ZWBIZCHECKDETAIL_MKID='GCKJ' AND ZWBIZCHECKDETAIL_DWBH='%1$s'  AND ZWBIZCHECKDETAIL_YEAR='%2$s' " +
                        "AND ZWBIZCHECKDETAIL_KJQJ='%3$s'",dwbh1,year,qj);
                int  count = db.getDatacountBysql(strSql);
                if (count>0){ //关账
                    returnObject.put("DWBH", dwbh1);
                    returnObject.put("MKBH", "01");
                    returnObject.put("ZT", "1");
                }else{ //没关账
                    returnObject.put("DWBH", dwbh1);
                    returnObject.put("MKBH", "01");
                    returnObject.put("ZT", "0");
                }
            }catch (Exception e) {
                log.error("query报错：" + e.getMessage(), e);
//                throw new Exception("异常="+e.getMessage());
            }
            result.add(returnObject);
        }

        return result;

    }





}
